package com.thinkgem.jeesite.modules.bu.utils;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import com.thinkgem.jeesite.modules.bu.entity.BuOrder;

public class ExcelTest3 {

	
	public static HSSFWorkbook hssfWorkbook;
	public static HSSFSheet hssfSheet;

	/**
	 * 获取第一个Sheet中的单元格
	 * 
	 * @param rowIndex
	 * @param colIndex
	 * @return
	 */
	public static Cell getCellInSheet(int rowIndex, int colIndex) {
		hssfSheet = hssfWorkbook.getSheetAt(0);
		Row row = hssfSheet.getRow(rowIndex - 1);
		Cell cell = row.getCell(colIndex - 1);
		return cell;
	}

	public static void main(String[] args) {
		try {
			reportOrderMergeXls(getEntity(), "zz", "yy");
			System.out.println("导出完成");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static BuOrder getEntity() {
		BuOrder order = new BuOrder();

		order.setId("0ec85e0f047449fc9bff67cb1b821ac6");
		order.setOrderNo("20180916201759");
		order.setOrderType("内部订单");
		order.setProductNo("1");
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		order.setOrderDate(format.format(new Date()));

		order.setShipDate(new Date());
		order.setProductName("1");
		order.setCrafts("1");
		order.setElectroplate("1");
		order.setBack("1");

		order.setPackages("1");
		order.setFitting("1");
		String picUrl = "http://image.baidu.com/search/detail?ct=503316480&z=0&ipn=d&word=%E5%9B%BE%E7%89%87&hs=0&pn=0&spn=0&di=156082395700&pi=0&rn=1&tn=baiduimagedetail&is=0%2C0&ie=utf-8&oe=utf-8&cl=2&lm=-1&cs=415293130%2C2419074865&os=1556766946%2C250663840&simid=4145280632%2C499508967&adpicid=0&lpn=0&ln=30&fr=ala&fm=&sme=&cg=&bdtype=0&oriquery=&objurl=http%3A%2F%2Fimg.zcool.cn%2Fcommunity%2F0117e2571b8b246ac72538120dd8a4.jpg%401280w_1l_2o_100sh.jpg&fromurl=ippr_z2C%24qAzdH3FAzdH3Fooo_z%26e3Bzv55s_z%26e3Bv54_z%26e3BvgAzdH3Fo56hAzdH3FZMTYoNTMzMDQ%3D_z%26e3Bip4s&gsm=0&islist=&querylist=";
		order.setPicUrl(picUrl);
		order.setMaterial("1");

		order.setQuantity("1");
		order.setSalesModelFee("1");
		order.setSalesPrice("1");
		order.setProduceFee("1");
		order.setProduceModelFee("1");

		order.setSalesTotalFee("1");
		order.setProduceTotalFee("1");
		order.setProfit("1");
		order.setCustomerName("业务员001");
		order.setSalesman("客户001");

		order.setSupplierName("厂商001");
		order.setOrderType("未发货");
		order.setDeliveryName("aaa");
		order.setDeliveryNumber("123");

		return order;
	}

	public static List<Map> getListmap() {
		List<Map> list = new ArrayList<Map>();
		Map map1 = new HashMap();
		map1.put("id", 1);
		map1.put("name", "a");
		map1.put("value1", "1a");
		map1.put("value2", "2a");
		map1.put("value3", "3a");

		Map map2 = new HashMap();
		map2.put("id", 2);
		map2.put("name", "b");
		map2.put("value1", "1b");
		map2.put("value2", "2b");
		map2.put("value3", "3b");

		Map map3 = new HashMap();
		map3.put("id", 3);
		map3.put("name", "c");
		map3.put("value1", "1c");
		map3.put("value2", "2c");
		map3.put("value3", "3c");

		list.add(map1);
		list.add(map2);
		list.add(map3);

		return list;

	}

	static String[] excelHeader = { "产品工艺", "name", "value", "value", "value" };

	public static Cell getCellInSheet(int st,int rowIndex,int colIndex){
		
		 hssfSheet = hssfWorkbook.getSheetAt(st);
		 Row rowst = hssfSheet.getRow(rowIndex - 1);
		 if(rowst == null){
			 rowst.setRowNum(Cell.CELL_TYPE_STRING);
			 Cell cellst = rowst.getCell(colIndex - 1);
			 return cellst;
		 }
		 Cell cellst = rowst.getCell(colIndex - 1);
		 return cellst;
	}
	
	/**
	 * 导出xls文件(合并单元格)
	 * 
	 * @param order
	 * @param xlsFileName
	 * @param sheetName
	 */
	public static void reportOrderMergeXls(BuOrder order, String xlsFileName, String sheetName) throws Exception {

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);// 创建一个sheet-test1

		// 设置单元格风格，居中对齐.
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		// 设置字体:
		HSSFFont font = wb.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 12);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示

		style.setFont(font);// 要用到的字体格式

		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 11));
		sheet.autoSizeColumn(0);// 自动设宽
		HSSFCell cell = row.createCell(0);
		cell.setCellValue("xxxyyy");
		cell.setCellStyle(style);
		
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 12, 13));
		row.createCell(12).setCellValue("xxxx");
		
		row = sheet.createRow(1);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 12));
		row.createCell(12).setCellValue("客户:");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 13));
		row.createCell(13).setCellValue("xxx");
		
		row = sheet.createRow(2);
		row.createCell(0).setCellValue("生产编号:");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
		row.createCell(2).setCellValue("xxx");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 7));
		row.createCell(8).setCellValue("产品名称:");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 9));
		row.createCell(10).setCellValue("xxx");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 10, 13));
		
		
		row = sheet.createRow(3);
		row.createCell(0).setCellValue("生产数量:");
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
		row.createCell(2).setCellValue("xxx");
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 7));
		row.createCell(8).setCellValue("下单日期:");
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 8, 9));
		row.createCell(10).setCellValue("xxxx");
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 10, 13));

		
		row = sheet.createRow(4);
		row.createCell(0).setCellValue("产品尺寸:");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
		row.createCell(2).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 3));
		row.createCell(4).setCellValue("厚度:");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 4, 5));
		row.createCell(6).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 7));
		row.createCell(8).setCellValue("出货日期:");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 8, 9));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 10, 13));
		row.createCell(10).setCellValue("xx");
		
		
		sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 13));
		
		row = sheet.createRow(6);
		row.createCell(0).setCellValue("材料	");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 1));
		row.createCell(2).setCellValue("背模	");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 2, 3));
		row.createCell(4).setCellValue("配件");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 4, 7));
		row.createCell(8).setCellValue("电镀");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 8, 10));
		row.createCell(11).setCellValue("部门流程");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 11, 11));
		row.createCell(12).setCellValue("负责人签字");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 12, 12));
		row.createCell(13).setCellValue("实际完成日期");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 13, 13));
		
		
		row = sheet.createRow(7);
		row.createCell(0).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 0, 1));
		row.createCell(2).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 2, 3));
		row.createCell(4).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 4, 7));
		row.createCell(8).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 8, 10));
		row.createCell(11).setCellValue("刻模");
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 11));
		row.createCell(12).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 12, 12));
		row.createCell(13).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 13, 13));
		
		
		row = sheet.createRow(8);
		row.createCell(11).setCellValue("压胚");
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 11, 11));
		row.createCell(12).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 12, 12));
		row.createCell(13).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 13, 13));
		
		
		row = sheet.createRow(9);
		row.createCell(11).setCellValue("焊针");
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 11, 11));
		row.createCell(12).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 12, 12));
		row.createCell(13).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 13, 13));
		
		
		row = sheet.createRow(10);
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 1));
		row.createCell(0).setCellValue("钻刻");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 2, 3));
		row.createCell(2).setCellValue("树脂");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 4, 5));
		row.createCell(4).setCellValue("抛光");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 6, 7));
		row.createCell(6).setCellValue("扫古");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 8, 10));
		row.createCell(8).setCellValue("包装");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 11, 11));
		row.createCell(11).setCellValue("抛光");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 12, 12));
		row.createCell(12).setCellValue("xxx");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 13, 13));
		row.createCell(13).setCellValue("xxx");
		
		
		row = sheet.createRow(11);
		row.createCell(0).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 0, 1));
		row.createCell(11).setCellValue("电镀");
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 11, 11));
		row.createCell(12).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 12, 12));
		row.createCell(13).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 13, 13));
		row.createCell(2).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 2, 3));
		row.createCell(4).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 4, 5));
		row.createCell(6).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 6, 7));
		row.createCell(8).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 8, 10));
		
		
		row = sheet.createRow(12);
		row.createCell(11).setCellValue("上色");
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 11, 11));
		row.createCell(12).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 12, 12));
		row.createCell(13).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 13, 13));
		
		row = sheet.createRow(13);
		row.createCell(11).setCellValue("包装");
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 11, 11));
		row.createCell(12).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 12, 12));
		row.createCell(13).setCellValue("xx");
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 13, 13));
		
		
		row = sheet.createRow(14);
		sheet.addMergedRegion(new CellRangeAddress(14, 14, 0, 13));
		row.createCell(0).setCellValue("图稿");
		
		row = sheet.createRow(15);
		sheet.addMergedRegion(new CellRangeAddress(15, 36, 0, 13));
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		
		System.out.println("picUrl--------->>>>>>>>>"+order.getPicUrl());
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();  
		// 将图片写入流中          
		BufferedImage bufferImg = ImageIO.read(getInputStream("http://localhost:8080/tomcat.png"));
		
		ImageIO. write(bufferImg, "PNG", outStream);
		// 利用HSSFPatriarch将图片写入EXCEL          
		HSSFPatriarch patri = sheet.createDrawingPatriarch();         
		HSSFClientAnchor anchor = new HSSFClientAnchor(50, 50, 50, 50,( short) 2, 16, (short ) 12,36); 
		patri.createPicture( anchor, wb.addPicture(           
		outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
		
		row = sheet.createRow(37);
		sheet.addMergedRegion(new CellRangeAddress(37, 38, 0, 0));
		row.createCell(0).setCellValue("备注:");
		sheet.addMergedRegion(new CellRangeAddress(37, 38, 1, 13));
		row.createCell(1).setCellValue("");
		
		
		row = sheet.createRow(39);
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 0, 0));
		row.createCell(0).setCellValue("跟单:");
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 1, 11));
		row.createCell(1).setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 12, 12));
		row.createCell(12).setCellValue("业务员:");
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 13, 13));
		row.createCell(13).setCellValue("xxx");
		
		
		/**
		 * 1、得到所有的合并单元格 sourceSheet.getNumMergedRegions();
		 * 2、得到某一个合并单元格 CellRangeAddress oldRange=sourceSheet.getMergedRegion(i);
		 * 3、起始行 oldRange.getFirstRow();
		 * 4、结束行oldRange.getLastRow();
		 * 5、oldRange.getFirstColumn();
		 * 6、oldRange.getLastColumn();
		 */
		int mergeGegions = sheet.getNumMergedRegions();
		System.out.println("mergeGegions------>>>>"+mergeGegions);
		for(int i = 0;i<mergeGegions;i++){
			org.apache.poi.ss.util.CellRangeAddress oldRange = sheet.getMergedRegion(i);
			int firstRow = oldRange.getFirstRow();
			int lastRow = oldRange.getLastRow();
			int firstColumn = oldRange.getFirstColumn();
			int lastColumn = oldRange.getLastColumn();
			System.out.println("firstRow------>>>>"+firstRow);
			System.out.println("lastRow------>>>>"+lastRow);
			System.out.println("firstColumn------>>>>"+firstColumn);
			System.out.println("lastColumn------>>>>"+lastColumn);
		}
		
		try {
			FileOutputStream fileOut = new FileOutputStream("E:/excel/" + new Date().getTime() + ".xls");
			wb.write(fileOut);
			fileOut.close();
			System.out.println("输出完成");
		} catch (Exception e) {
			System.out.println("文件输出失败!");
			e.printStackTrace();
		}

	}
	
	public static  InputStream getInputStream(String path){ 
		InputStream inputStream=null ;         
		HttpURLConnection httpURLConnection=null ;      
		try{             
			URL url= new URL(path );      
			if(url !=null){      
				httpURLConnection=(HttpURLConnection) url.openConnection();  
				httpURLConnection.setConnectTimeout(3000);          
				httpURLConnection.setRequestMethod("GET" );      
				int responseCode=httpURLConnection .getResponseCode();    
				if(responseCode ==200){             
					inputStream=httpURLConnection .getInputStream();   
					} 
				}
			} catch(Exception e ){
				e.printStackTrace();  
				}
			return inputStream ;
		}



	/**
	 * 导出xls文件(合并单元格)
	 * 
	 * @param list
	 * @param xlsFileName
	 * @param sheetName
	 */
	public static void reportMergeXls(List<Map> list, String xlsFileName, String sheetName,HttpServletResponse response) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);// 创建一个sheet-test1

		// 设置单元格风格，居中对齐.
		HSSFCellStyle cs = wb.createCellStyle();
		cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		cs.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		cs.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		// 设置字体:
		HSSFFont font = wb.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 12);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示

		cs.setFont(font);// 要用到的字体格式

		// sheet.setColumnWidth(0, 3766); //第一个参数代表列下标(从0开始),第2个参数代表宽度值
		// cs.setWrapText(true);//设置字体超出宽度自动换行

		// 设置背景颜色
		// cs.setFillBackgroundColor(HSSFColor.BLUE.index);
		// cs.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

		// 创建第一行
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell;

		for (int i = 0; i < excelHeader.length; i++) {// 设置表头-标题
			cell = row.createCell(i);
			cell.setCellValue(excelHeader[i]);
			cell.setCellStyle(cs);
			sheet.autoSizeColumn(i);// 自动设宽
		}

		// 设置合并的标题头(注意：横向合并的时候，标题头单元格必须长度和内容单元格一致否则合并时会把其他标题头单元格内容挤掉)
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 11)); // 产品工艺(动态配置由程序填充)
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 12, 13)); // 空白处

		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 6));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 13));

		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 6));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 8));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 9, 13));

		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 3));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 4, 5));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 7));

		sheet.addMergedRegion(new CellRangeAddress(4, 4, 8, 9));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 10, 13));

		sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 13));

		sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 2, 3));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 4, 7));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 8, 10));

		sheet.addMergedRegion(new CellRangeAddress(6, 6, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(7, 9, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 2, 3));
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 4, 7));
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 8, 10));

		sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(8, 8, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(9, 9, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 2, 3));
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 4, 5));
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 6, 7));
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 8, 10));
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(11, 13, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 2, 3));
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 4, 5));
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 6, 7));
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 8, 10));

		sheet.addMergedRegion(new CellRangeAddress(11, 11, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(12, 12, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(13, 13, 11, 11));
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 13, 13));

		sheet.addMergedRegion(new CellRangeAddress(14, 14, 0, 13));

		sheet.addMergedRegion(new CellRangeAddress(15, 37, 0, 13));

		sheet.addMergedRegion(new CellRangeAddress(38, 39, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(38, 39, 2, 13));

		sheet.addMergedRegion(new CellRangeAddress(40, 40, 0, 0));
		sheet.addMergedRegion(new CellRangeAddress(40, 40, 1, 11));
		sheet.addMergedRegion(new CellRangeAddress(40, 40, 12, 12));
		sheet.addMergedRegion(new CellRangeAddress(40, 40, 13, 13));

		// sheet.addMergedRegion(new
		// CellRangeAddress(0,0,2,4));//横向：合并第一行的第2列到第4列
		// sheet.addMergedRegion(new
		// CellRangeAddress(0,1,0,0));//纵向：合并第一列的第1行和第2行第
		// sheet.addMergedRegion(new
		// CellRangeAddress(0,1,1,1));//纵向：合并第二列的第1行和第2行第

		// 设置对应的合并单元格标题
		row = sheet.createRow(1);
		for (int j = 2; j < 5; j++) {
			cell = row.createCell((short) j);
			cell.setCellStyle(cs);
			cell.setCellValue("value" + (j - 1));
			sheet.autoSizeColumn(j);// 自动设宽
		}
		// 设置列值-内容
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 2);
			Map map = list.get(i);
			row.createCell(0).setCellValue((int) map.get("id"));
			row.createCell(1).setCellValue((String) map.get("name"));
			row.createCell(2).setCellValue((String) map.get("value1"));
			row.createCell(3).setCellValue((String) map.get("value2"));
			row.createCell(4).setCellValue((String) map.get("value3"));
		}
		
		try {
			String fileName = "E:/excel/" + new Date().getTime() + ".xls";
			FileOutputStream fileOut = new FileOutputStream(fileName);
			wb.write(fileOut);
			fileOut.close();
			System.out.println("输出完成");
			//页面弹出下载或保存 
			response.setContentType("application/x-download");
			response.setHeader("Content-disposition","attachment;filename="+
			new String(fileName.getBytes("utf-8"), "iso-8859-1"));
			response.setCharacterEncoding("utf-8"); 
			OutputStream os = response.getOutputStream();
			wb.write(os);
			os.close();
		} catch (Exception e) {
			System.out.println("文件输出失败!");
			e.printStackTrace();
		}

	}
	
	

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 *            行下标
	 * @param column
	 *            列下标
	 * @return
	 */
	public static boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			org.apache.poi.ss.util.CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

}
